﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_IndexesAll]
#-- Purpose:		Provides a selective mechanism for index maintenance
#--	Last Update:	06/21/2016
#--					For a complete history - please review comments in Version
#--					control
#-- Called By:		Scheduled Job
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_IndexesAll]
(
	@skip_heaps_stats		bit			= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@db_name sysname

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT		db.name
	FROM		[master].[sys].[databases] db
	JOIN		[master].[sys].[master_files] mf ON db.database_id = mf.database_id
				AND mf.type = 0
	WHERE		db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
	GROUP BY	db.name
	ORDER BY	SUM(mf.size)
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC [dbo].[spMaint_Indexes]	@database_name = @db_name,
									@skip_heaps_stats = @skip_heaps_stats;

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF
